Create Table dbo.opengiving_map_contactUserID
(
      contact_id    int Identity(1,1)   Not Null
    , userID        uniqueidentifier    Not Null
    
    Constraint PK_opengiving_map_contactUserID
        Primary Key Clustered(contact_id)
);


Create Table dbo.opengiving_ref_attributeCategory
(
      category_id   tinyint Identity(1,1)   Not Null
    , category      varchar(50)             Not Null
    , isActive      bit                     Not Null

    Constraint PK_opengiving_ref_attributeCategory
        Primary Key Clustered(category_id)
);

Create Table dbo.opengiving_ref_attribute
(
      attribute_id  tinyint Identity(1,1)   Not Null
    , category_id   tinyint                 Not Null
    , attribute     varchar(50)             Not Null
    
    Constraint PK_opengiving_ref_attribute
        Primary Key Clustered(attribute_id)

    Constraint FK_opengiving_ref_attribute_categoryID
        Foreign Key (category_id)
        References dbo.opengiving_ref_attributeCategory(category_id)
);

Create Unique NonClustered Index IX_opengiving_ref_attribute_unique
    On dbo.opengiving_ref_attribute(attribute_id, category_id);

Create Table dbo.opengiving_ref_attributeValue
(
      value_id      smallint Identity(1,1)  Not Null
    , attribute_id  tinyint                 Not Null
    
    Constraint PK_opengiving_ref_attributeValue
        Primary Key Clustered(value_id)
);

Create Table dbo.opengiving_ref_organization
(
      organization_id   smallint Identity(1,1)  Not Null
    , organization      varchar(50)             Not Null
    , isActive          bit                     Not Null
    
    Constraint PK_opengiving_ref_organization
        Primary Key Clustered(organization_id)
);

--Create Table dbo.opengiving_contact
--(
--      contact_id        int Identity(1,1)   Not Null
--    , lastName          varchar(20)         Not Null
--    , firstName         varchar(20)         Not Null
--    , phone             varchar(20)         Not Null
--    , address_street1   varchar(50)         Not Null
--    , address_street2   varchar(50)         Null
--    , address_city      varchar(50)         Not Null
--    , address_state     char(2)             Not Null
--    , address_zip       varchar(10)         Not Null
--    , address_country   char(2)             Not Null
--    , birthday          date                Not Null
--    , gender            char(1)             Null
--    , employer          varchar(50)         Null
--    , organization_id   tinyint             Null
    
--    Constraint PK_opengiving_contact
--        Primary Key Clustered(contact_id)
--);

Create Table dbo.opengiving_map_contactAttribute
(
      contact_id    int         Not Null
    , attribute_id  tinyint     Not Null
    , value_id      smallint    Not Null

    Constraint PK_opengiving_map_contactAttribute
        Primary Key Clustered(contact_id, attribute_id)

    , Constraint FK_opengiving_map_contactAttribute_contactID
        Foreign Key (contact_id)
        References dbo.opengiving_map_contactUserID(contact_id)
    
    , Constraint FK_opengiving_map_contactAttribute_attributeID
        Foreign Key (attribute_id)
        References dbo.opengiving_ref_attribute(attribute_id)
    
    , Constraint FK_opengiving_map_contactAttribute_valueID
        Foreign Key (value_id)
        References dbo.opengiving_ref_attributeValue(value_id)
);

Create Table dbo.opengiving_ref_neighborhood
(
      neighborhood_id   tinyint Identity(1,1)   Not Null
    , neighborhood      varchar(50)             Not Null
    
    Constraint PK_opengiving_ref_neighborhood
        Primary Key (neighborhood_id)
);

Create Table dbo.opengiving_event
(
      event_id                  int Identity(1,1)   Not Null 
    , eventName                 varchar(50)         Not Null
    , startTime                 smalldatetime       Not Null
    , endTime                   smalldatetime       Not Null
    , address_street            varchar(50)         Not Null
    , address_city              varchar(50)         Not Null
    , address_state             char(2)             Not Null
    , address_zip               varchar(10)         Not Null
    , address_country           char(2)             Not Null
    , neighborhood_id           tinyint             Not Null
    , url                       varchar(255)        Not Null
    , briefDescription          varchar(64)         Not Null
    , fullDescription           varchar(255)        Not Null
    , organization_id           smallint            Not Null
    , contact_id                int                 Not Null
    , volunteerMax              tinyint             Not Null
    , sameDayRegistration       bit                 Not Null
    , registrationCutOff        smalldatetime       Not Null
    , isCancelled               bit                 Not Null

    Constraint PK_opengiving_event
        Primary Key Clustered(event_id)

    , Constraint FK_opengiving_event_contactID
        Foreign Key (contact_id)
        References dbo.opengiving_map_contactUserID(contact_id)

    , Constraint FK_opengiving_event_organizationID
        Foreign Key (organization_id)
        References dbo.opengiving_ref_organization(organization_id)

    , Constraint FK_opengiving_event_neighborhoodID
        Foreign Key (neighborhood_id)
        References dbo.opengiving_ref_neighborhood(neighborhood_id)

);

Create Table dbo.opengiving_eventRegistration
(
      event_id          int             Not Null
    , contact_id        int             Not Null
    , organization_id   smallint        Not Null
    , dateRegistered    smalldatetime   Not Null
    , dateUnregistered  smalldatetime   Null
    , attended          bit             Not Null
    
    Constraint PK_opengiving_eventRegistration
        Primary Key Clustered(event_id, contact_id)

    , Constraint FK_opengiving_eventRegistration_eventID
        Foreign Key (event_id)
        References dbo.opengiving_event(event_id)

    , Constraint FK_opengiving_eventRegistration_contactID
        Foreign Key (contact_id)
        References dbo.opengiving_map_contactUserID(contact_id)
);

Create Table dbo.opengiving_ref_group
(
      group_id      tinyint Identity(1,1)   Not Null
    , groupName     varchar(50)             Not Null
    
    Constraint PK_opengiving_ref_group
        Primary Key Clustered(group_id)
);

Create Table dbo.opengiving_map_contactGroup
(
      contact_id    int         Not Null
    , group_id      tinyint     Not Null
      
    Constraint PK_opengiving_map_contactGroup
        Primary Key Clustered(contact_id, group_id)

    , Constraint FK_opengiving_map_contactGroup_contactID
        Foreign Key (contact_id)
        References dbo.opengiving_map_contactUserID(contact_id)
    
    , Constraint FK_opengiving_map_contactGroup_groupID
        Foreign Key (group_id)
        References dbo.opengiving_ref_group(group_id)
);

Select * From sys.tables Where name Like 'open%' order by name
